USE master;
GO

IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetEmployeeCollection]'))
DROP PROCEDURE dbo.GetEmployeeCollection
GO
IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetSingleEmployee]'))
DROP PROCEDURE dbo.GetSingleEmployee
GO
IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetDepartmentCollection]'))
DROP PROCEDURE dbo.GetDepartmentCollection
GO
IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetSingleDepartment]'))
DROP PROCEDURE dbo.GetSingleDepartment
GO
IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetJobTitleCollection]'))
DROP PROCEDURE dbo.GetJobTitleCollection
GO
IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetSingleJobTitle]'))
DROP PROCEDURE dbo.GetSingleJobTitle
GO
IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetEmployeeTypeCollection]'))
DROP PROCEDURE dbo.GetEmployeeTypeCollection
GO
IF EXISTS(select 1 from sys.objects where type = N'P' AND object_id = OBJECT_ID(N'[dbo].[GetSingleEmployeeType]'))
DROP PROCEDURE dbo.GetSingleEmployeeType
--
--
IF EXISTS(select 1 from sys.objects where type = N'F' AND object_id = OBJECT_ID(N'[dbo].[FK_Employees_JobTitles]'))
DROP PROCEDURE dbo.FK_Employees_JobTitles
GO
IF EXISTS(select 1 from sys.objects where type = N'F' AND object_id = OBJECT_ID(N'[dbo].[FK_Employees_Departments]'))
DROP PROCEDURE dbo.FK_Employees_Departments
GO
IF EXISTS(select 1 from sys.objects where type = N'F' AND object_id = OBJECT_ID(N'[dbo].[FK_Employees_EmployeeTypes]'))
DROP PROCEDURE dbo.FK_Employees_EmployeeTypes
--
--
IF EXISTS(select 1 from sys.objects where type = N'U' AND object_id = OBJECT_ID(N'[dbo].[Employees]'))
DROP PROCEDURE dbo.Employees
GO
IF EXISTS(select 1 from sys.objects where type = N'U' AND object_id = OBJECT_ID(N'[dbo].[Departments]'))
DROP PROCEDURE dbo.Departments
GO
IF EXISTS(select 1 from sys.objects where type = N'U' AND object_id = OBJECT_ID(N'[dbo].[JobTitles]'))
DROP PROCEDURE dbo.JobTitles
GO
IF EXISTS(select 1 from sys.objects where type = N'U' AND object_id = OBJECT_ID(N'[dbo].[EmployeeTypes]'))
DROP PROCEDURE dbo.EmployeeTypes
GO
--
--
IF DB_ID (N'CapstoneTraining') IS NOT NULL
DROP DATABASE CapstoneTraining;
GO
-- Get the SQL Server data path.
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

 -- Execute the CREATE DATABASE statement.
EXECUTE ('CREATE DATABASE CapstoneTraining
ON PRIMARY 
    (
    NAME = FileStreamDB_data 
    ,FILENAME = ''' + @data_path + 'CapstoneTraining_data.mdf''
    ,SIZE = 10MB
    ,MAXSIZE = 50MB
    ,FILEGROWTH = 15%
    ) 
LOG ON
    (
    NAME = FileStream_log
    ,FILENAME = ''' + @data_path + 'CapstoneTraining_log.ldf''
    ,SIZE = 5MB
    ,MAXSIZE = 25MB
    ,FILEGROWTH = 5MB
    )'
);
GO

USE CapstoneTraining;
GO

CREATE TABLE EmployeeTypes
(
	ID int PRIMARY KEY NOT NULL IDENTITY(1,1),
	TypeName nvarchar(50),
	Description nvarchar(MAX)
)
GO

CREATE TABLE JobTitles
(
	ID int PRIMARY KEY NOT NULL IDENTITY(1,1),
	TitleName nvarchar(50),
	Description nvarchar(MAX)
)
GO

CREATE TABLE Departments
(
	ID int PRIMARY KEY NOT NULL IDENTITY(1,1),
	DeptName nvarchar(50),
	Description nvarchar(MAX)
)
GO

CREATE TABLE Employees
(
	ID int PRIMARY KEY NOT NULL IDENTITY(1,1),
	Name nvarchar(50) NOT NULL,
	Description nvarchar(MAX),
	JobTitleID int,
	DepartmentID int,
	EmployeeTypeID int,
	CONSTRAINT FK_Employees_JobTitles Foreign Key (JobTitleID) references JobTitles(ID),
	CONSTRAINT FK_Employees_Departments Foreign Key (DepartmentID) references Departments(ID),
	CONSTRAINT FK_Employees_EmployeeTypes Foreign Key (EmployeeTypeID) references EmployeeTypes(ID)
)
GO

--
--Stored Procedures
--
CREATE PROCEDURE GetEmployeeCollection
AS
BEGIN
	SET NOCOUNT ON;

	SELECT e.ID, e.Name, j.TitleName, j.ID AS JobTitleID, d.ID AS DepartmentID, d.DeptName, et.ID AS EmployeeTypeID, et.TypeName
	FROM Employees e
	INNER JOIN JobTitles j
		ON j.ID = e.JobTitleID
	INNER JOIN Departments d
		ON d.ID = e.DepartmentID
	INNER JOIN EmployeeTypes et
		ON et.ID = e.EmployeeTypeID
END

GO

CREATE PROCEDURE GetSingleEmployee
@EmployeeID int=NULL
AS
BEGIN
	SET NOCOUNT ON;

	SELECT e.ID, e.Name, e.Description, j.TitleName, j.ID AS JobTitleID, d.ID AS DepartmentID, d.DeptName, et.ID AS EmployeeTypeID, et.TypeName
	FROM Employees e
	INNER JOIN JobTitles j
		ON j.ID = e.JobTitleID
	INNER JOIN Departments d
		ON d.ID = e.DepartmentID
	INNER JOIN EmployeeTypes et
		ON et.ID = e.EmployeeTypeID
	WHERE e.ID =@EmployeeID
END

GO

CREATE PROCEDURE GetDepartmentCollection
AS
BEGIN
	SET NOCOUNT ON;

	SELECT d.ID, d.DeptName, d.Description
	FROM Departments d
END

GO

CREATE PROCEDURE GetSingleDepartment
@deptID int=NULL
AS
BEGIN
	SET NOCOUNT ON;

	SELECT d.ID, d.DeptName, d.Description
	FROM Departments d
	WHERE d.ID = @deptID
END

GO

CREATE PROCEDURE GetJobTitleCollection
AS
BEGIN
	SET NOCOUNT ON;

	SELECT j.ID, j.TitleName, j.Description
	FROM JobTitles j
END

GO

CREATE PROCEDURE GetSingleJobTitle
@jTitleID int=NULL
AS
BEGIN
	SET NOCOUNT ON;

	SELECT j.ID, j.TitleName, j.Description
	FROM JobTitles j
	WHERE j.ID=@jTitleID
END

GO

CREATE PROCEDURE GetEmployeeTypeCollection
AS
BEGIN
	SET NOCOUNT ON;

	SELECT et.ID, et.TypeName, et.Description
	FROM EmployeeTypes et
END

GO

CREATE PROCEDURE GetSingleEmployeeType
@eTypeID int=NULL
AS
BEGIN
	SET NOCOUNT ON;

	SELECT e.ID, e.TypeName, e.Description
	FROM EmployeeTypes e
	WHERE e.ID=@eTypeID
END

GO

--
--Sample Data
--
SET IDENTITY_INSERT EmployeeTypes ON;
INSERT INTO EmployeeTypes(ID, TypeName, [Description]) VALUES (1, 'Full time', 'Employees of this type will be at work full working-day.');
INSERT INTO EmployeeTypes(ID, TypeName, [Description]) VALUES (2, 'Part time', 'Employees of this type will be at work as signed in the contract.');
SET IDENTITY_INSERT EmployeeTypes OFF;
GO

SET IDENTITY_INSERT Departments ON;
INSERT INTO Departments(ID, DeptName, [Description]) VALUES (1, 'Dept 1', 'Dept 1.');
INSERT INTO Departments(ID, DeptName, [Description]) VALUES (2, 'Dept 2', 'Dept 2.')
SET IDENTITY_INSERT Departments OFF;
GO

SET IDENTITY_INSERT JobTitles ON;
INSERT INTO JobTitles(ID, TitleName, [Description]) VALUES (1, 'Developer', 'Developer.');
INSERT INTO JobTitles(ID, TitleName, [Description]) VALUES (2, 'PM', 'Project Manager.');
SET IDENTITY_INSERT JobTitles OFF;
GO

SET IDENTITY_INSERT Employees ON;
INSERT INTO Employees(ID, Name, [Description], JobTitleID, DepartmentID, EmployeeTypeID) VALUES (1, 'Duong Quang Anh', 'A good, deligent person.', 2, 1, 1);
INSERT INTO Employees(ID, Name, [Description], JobTitleID, DepartmentID, EmployeeTypeID) VALUES (2, 'Hoang Long', 'A good, deligent person.', 1, 1, 2);
SET IDENTITY_INSERT Employees OFF;
GO